package com.minisoft.utils.reportdata;

import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.lang.Console;
import cn.hutool.core.util.StrUtil;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;

import java.nio.charset.StandardCharsets;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author JueSu
 * @description: TODO
 * @date 2022/7/4 11:03
 */
public class FaceData {

    public static String DB_CENTER = "center-pro";
    public static void main(String[] args) throws SQLException {
        List<String> projectStrs = FileUtil.readUtf8Lines("C:\\Users\\31594\\Result_28.csv");
        List<String> faceStrs = FileUtil.readUtf8Lines("C:\\Users\\31594\\Result_32.csv");
        List<Entity> entityList = Db.use(DB_CENTER).query("select r.sale_team_id,r.project_id from sale_team_sale_org_rel r where r.deleted = 0");
        Map<String,String> prj2SaleTeam = new HashMap<>();
        for (Entity entity : entityList) {
            prj2SaleTeam.put(entity.getStr("project_id"),entity.getStr("sale_team_id"));
        }
        Map<String,String> prj2HfPrj = new HashMap<>();
        for (String projectStr : projectStrs) {
            String[] split = projectStr.split(",");
            prj2HfPrj.put(split[2],split[1]);
        }


        List<String> sqls = new ArrayList<>();
        for (String faceStr : faceStrs) {
            String[] split = faceStr.split(",");
            String pid = prj2HfPrj.get(split[3]);
            String saleTeamId = prj2SaleTeam.get(pid);
            String mobile = split[2];
            DateTime parse = DateUtil.parse(split[1], "yyyy-MM-dd'T'HH:mm:ss");
            DateTime dateTime = DateUtil.offsetHour(parse, 8);
            sqls.add(StrUtil.format("INSERT INTO tmp_face_mobile (mobile,first_visit_time,project_id,sale_team_id) VALUES ('{}','{}','{}','{}');",mobile,dateTime,pid,saleTeamId));
//            Console.log("INSERT INTO tmp_wxb_mobile (mobile,first_visit_time,project_id,sale_team_id) VALUES ('{}','{}','{}','{}');",mobile,dateTime,pid,saleTeamId);
        }

        ExcelReader reader = ExcelUtil.getReader("C:\\Users\\31594\\Downloads\\2022风控刷证客户（新科技）.xlsx");
        for (Map<String, Object> objectMap : reader.readAll()) {
            Object pid = objectMap.get("项目ID");
            Object mobile = objectMap.get("客户电话");
            Object dateTime = objectMap.get("首次抓拍时间");
            String saleTeamId = prj2SaleTeam.get(pid);
            if (dateTime != null) {
                sqls.add(StrUtil.format("INSERT INTO tmp_face_mobile (mobile,first_visit_time,project_id,sale_team_id) VALUES ('{}','{}','{}','{}');",mobile,dateTime,pid,saleTeamId));
//                Console.log("INSERT INTO tmp_wxb_mobile (mobile,first_visit_time,project_id,sale_team_id) VALUES ('{}','{}','{}','{}');",mobile,dateTime,pid,saleTeamId);
            }
        }

        FileUtil.writeLines(sqls,"C:\\Users\\31594\\Downloads\\faceSql.sql", StandardCharsets.UTF_8);

    }
}
